Transaction and Lock¶

Transaction Commit¶

Updates that occurred in the database are not permanently stored until the COMMIT WORK statement is executed. "Permanently stored" means that storing the updates in the disk is completed; The WORK keyword can be omitted. In addition, other users of the database cannot see the updates until they are permanently applied. For example, when a new row is inserted into a class, only the user who inserted the row can access it until the database transaction is committed. (If the UNCOMMITTED INSTANCES isolation level is used, other users can see inconsistent uncommitted updates.)

All locks obtained by the transaction are released after the transaction is committed.

COMMIT [WORK];

-- ;autocommit off
-- AUTOCOMMIT IS OFF

SELECT name, seats
FROM stadium WHERE code IN (30138, 30139, 30140);

    name                                seats
=============================================
    'Athens Olympic Tennis Centre'      3200
    'Goudi Olympic Hall'                5000
    'Vouliagmeni Olympic Centre'        3400

The following UPDATE statement changes three column values of seats from the stadium. To compare the results, check the current values and names before the update is done. Since csql runs in an autocommit mode by default, the following example is executed after setting the autocommit mode to off.

UPDATE stadium
SET seats = seats + 1000
WHERE code IN (30138, 30139, 30140);

SELECT name, seats FROM stadium WHERE code in (30138, 30139, 30140);

    name                                seats
============================================
    'Athens Olympic Tennis Centre'      4200
    'Goudi Olympic Hall'                6000
    'Vouliagmeni Olympic Centre'        4400

If the update is properly done, the changes can be permanently fixed. In this time, use the COMMIT WORK as below:

COMMIT [WORK];

An auto-commit mode is a mode that commits or rolls back all SQL statements. The transaction is committed automatically if the SQL is executed successfully, or is rolled back automatically if an error occurs. Such auto commit modes are supported in any interfaces.

In CCI, PHP, ODBC and OLE DB interfaces, you can configure auto-commit mode by using CCI_DEFAULT_AUTOCOMMIT upon startup of an application. If configuration on broker parameter is omitted, the default value is set to ON. To change auto-commit mode, use the following functions by interface: cci_set_autocommit () for CCI interface and cubrid_set_autocommit () for PHP interface.

For session command (;AUtocommit) which enables auto-commit configuration in CSQL Interpreter, see Session Commands.

Transaction Rollback¶

The ROLLBACK WORK statement removes all updates to the database since the last transaction. The WORK keyword can be omitted. By using this statement, you can cancel incorrect or unnecessary updates before they are permanently applied to the database. All locks obtained during the transaction are released.

ROLLBACK [WORK];

The following example shows two commands that modify the definition and the row of the same table.

-- csql> ;autocommit off
CREATE TABLE code2 (
    s_name  CHAR(1),
    f_name  VARCHAR(10)
);
COMMIT;

ALTER TABLE code2 DROP s_name;
INSERT INTO code2 (s_name, f_name) VALUES ('D','Diamond');

ERROR: s_name is not defined.

The INSERT statement fails because the s_name column has been dropped in the definition of code. The data intended to be entered to the code table is correct, but the s_name column is wrongly removed. At this point, you can use the ROLLBACK WORK statement to restore the original definition of the code table.

ROLLBACK WORK;

Later, remove the s_name column by entering the ALTER TABLE again and modify the INSERT statement. The INSERT command must be entered again because the transaction has been aborted. If the database update has been done as intended, commit the transaction to make the changes permanent.

ALTER TABLE code DROP s_name;
INSERT INTO code (f_name) VALUES ('Diamond');

COMMIT WORK;

Savepoint and Partial Rollback¶

A savepoint is established during the transaction so that database changes made by the transaction are rolled back to the specified savepoint. Such operation is called a partial rollback. In a partial rollback, database operations (insert, update, delete, etc.) after the savepoint are rolled back, and transaction operations before it are not rolled back. The transaction can proceed with other operations after the partial rollback is executed. Or the transaction can be terminated with the COMMIT WORK or ROLLBACK WORK statement. Note that the savepoint does not commit the changes made by the transaction.

A savepoint can be created at a certain point of the transaction, and multiple savepoints can be used for a certain point. If a partial rollback is executed to a savepoint before the specified savepoint or the transaction is terminated with the COMMIT WORK or ROLLBACK WORK statement, the specified savepoint is removed. The partial rollback after the specified savepoint can be performed multiple times.

Savepoints are useful because intermediate steps can be created and named to control long and complicated utilities. For example, if you use a savepoint during the update operation, you don't need to perform all statements again when you made a mistake.

SAVEPOINT mark;
mark:
_ a SQL identifier
_ a host variable (starting with :)

If you make mark all the same value when you specify multiple savepoints in a single transaction, only the latest savepoint appears in the partial rollback. The previous savepoints remain hidden until the rollback to the latest savepoint is performed and then appears when the latest savepoint disappears after being used.

ROLLBACK [WORK] [TO [SAVEPOINT] mark] ;
mark:
_ a SQL identifier
_ a host variable (starting with :)

Previously, the ROLLBACK WORK statement canceled all database changes added since the latest transaction. The ROLLBACK WORK statement is also used for the partial rollback that rolls back the transaction changes after the specified savepoint.

If mark value is not given, the transaction terminates canceling all changes including all savepoints created in the transaction. If mark value is given, changes after the specified savepoint are canceled and the ones before it are remained.

The below example shows how to rollback a part of a transaction. Firstly, specify savepoints SP1, SP2.

-- csql> ;autocommit off

CREATE TABLE athlete2 (name VARCHAR(40), gender CHAR(1), nation_code CHAR(3), event VARCHAR(30));
INSERT INTO athlete2(name, gender, nation_code, event)
VALUES ('Lim Kye-Sook', 'W', 'KOR', 'Hockey');
SAVEPOINT SP1;

SELECT * from athlete2;
INSERT INTO athlete2(name, gender, nation_code, event)
VALUES ('Lim Jin-Suk', 'M', 'KOR', 'Handball');

SELECT * FROM athlete2;
SAVEPOINT SP2;

RENAME TABLE athlete2 AS sportsman;
SELECT * FROM sportsman;
ROLLBACK WORK TO SP2;

In the example above, the name change of the athlete2 table is rolled back by the partial rollback. The following example shows how to execute the query with the original name and examining the result.

SELECT * FROM athlete2;
DELETE FROM athlete2 WHERE name = 'Lim Jin-Suk';
SELECT * FROM athlete2;
ROLLBACK WORK TO SP2;

In the example above, deleting 'Lim Jin-Suk' is discarded by rollback work to SP2 command. The following example shows how to roll back to SP1.

SELECT * FROM athlete2;
ROLLBACK WORK TO SP1;
SELECT * FROM athlete2;
COMMIT WORK;

Granularity Locking¶

CUBRID uses a granularity locking protocol to decrease the number of locks. In the granularity locking protocol, a database can be modeled as a hierarchy of lockable units: row lock, table lock and database lock. Bigger locks have more granular locks.

If the locking granularities overlap, effects of a finer granularity are propagated in order to prevent conflicts. That is, if a shared lock is required on an instance of a table, an intent shared lock will be set on the table. If an exclusive lock is required on an instance of a table, an intent exclusive lock will be set on the table. An intent shared lock on a table means that a shared lock can be set on an instance of the table. An intent exclusive lock on a table means that a shared/exclusive lock can be set on an instance of the table. That is, if an intent shared lock on a table is allowed in one transaction, another transaction cannot obtain an exclusive lock on the table (for example, to add a new column). However, the second transaction may obtain a shared lock on the table. If an intent exclusive lock on the table is allowed in one transaction, another transaction cannot obtain a shared lock on the table (for example, a query on an instance of the tables cannot be executed because it is being changed).

A mechanism called lock escalation is used to limit the number of locks being managed. If a transaction has more than a certain number of locks (a number which can be changed by the lock_escalation system parameter), the system begins to require locks at the next higher level of granularity. This escalates the locks to a coarser level of granularity. CUBRID performs lock escalation when no transactions have a higher level of granularity in order to avoid a deadlock caused by lock conversion.

Lock Mode Types And Compatibility¶

CUBRID determines the lock mode depending on the type of operation to be performed by the transaction, and determines whether or not to share the lock depending on the mode of the lock preoccupied by another transaction. Such decisions concerning the lock are made by the system automatically. Manual assignment by the user is not allowed. To check the lock information of CUBRID, use the cubrid lockdb db_name command. For details, see Checking Lock Status.

• Shared lock (shared lock, S_LOCK)

  This lock is obtained before the read operation is executed on the object.

  It can be obtained by multiple transactions for the same object. At this time, transaction T2 and T3 can perform the read operation on the object concurrently, but not the update operation.

  Note

  • If an isolation level is REPEATABLE COMMITTED(4), Transaction T1 obtains the shared lock first before it performs the read operation on a certain object, and releases it immediately after it completes the operation even before transaction T1 is committed; therefore, one of other transactions can do the update or delete operation to this object.
  • If an isolation level is REPEATABLE READ(5), it keeps shared locks until a transaction T1 is committed; therefore, one of other transactions cannot do the update or delete operation to this object.

• Exclusive lock (exclusive lock, X_LOCK)

  This lock is obtained before the update operation is executed on the object.

  It can only be obtained by one transaction. Transaction T1 obtains the exclusive lock first before it performs the update operation on a certain object X, and does not release it until transaction T1 is committed even after the update operation is completed. Therefore, transaction T2 and T3 cannot perform the read operation as well on X before transaction T1 releases the exclusive lock.

• Update lock (update lock, U_LOCK)

  This lock is obtained when the read operation is executed in the expression before the update operation is performed. That is, this is used in UPDATE ... WHERE statement or DELETE ... WHERE statement.

  For example, when an UPDATE statement combined with a WHERE clause is executed, execute the operation by obtaining the update lock for each row and the exclusive lock only for the result rows that satisfy the condition when performing index search or full scan search in the WHERE clause. The update lock is converted to an exclusive lock when the actual update operation is performed. It can be called a quasi-exclusive lock because it does not allow read lock on the same object for another transaction.

• Intent lock

  This lock is set inherently in a higher-level object than a certain object to protect the lock on the object of a certain level.

  For example, when a shared lock is requested for a certain row, prevent a situation from occurring in which the table is locked by another transaction by setting the intent shared lock as well on the table at the higher level in hierarchy. Therefore, the intent lock is not set on rows at the lowest level, but is set on higher-level objects. The types of intent locks are as follows:

  • Intent shared lock (IS_LOCK)

    If the intent shared lock is set on the table, which is the higher-level object, as a result of the shared lock set on a certain row, another transaction cannot perform operations such as changing the schema of the table (e.g. adding a column or changing the table name) or updating all rows. However updating some rows or viewing all rows is allowed.

  • Intent exclusive lock (IX_LOCK)

    If the intent exclusive lock is set on the table, which is the higher-level object, as a result of the exclusive lock set on a certain row, another transaction cannot perform operations such as changing the schema of the table, updating or viewing all rows. However updating some rows is allowed.

  • Shared with intent exclusive lock(SIX_LOCK)

    This lock is set on the higher-level object inherently to protect the shared lock set on all objects at the lower hierarchical level and the intent exclusive lock on some object at the lower hierarchical level.

    Once the shared intent exclusive lock is set on a table, another transaction cannot change the schema of the table, update all/some rows or view all rows. However, viewing some rows is allowed.

• Key Lock

  A key lock is acquired when doing SELECT, INSERT, UPDATE or DELETE operation about a record with a unique key. However, to acquire a key lock, a unique key must exist on the WHERE condition's column.

  For example, if you INSERT a value, X_LOCK is acquired to this value and NS_LOCK is acuquired to the next key of this value.

  If you UPDATE/DELETE a value, NX_LOCKs are acquired at the closest smaller key than the defined minimum range and at the closest larger key than the defined maximum range.

  • Next-key shared lock, NS_LOCK

    When you perform an INSERT operation on a row that a unique key exists, acquire next key lock to protect the range that affect their task.

  • Next-key exclusive lock, NX_LOCK

    When you perform an UPDATE or a DELETE operation on rows that a unique key exists, acquire next and previous key lock to protect the range that affect their task.

• Schema Lock

  A shcema lock is acquired when executing DDL work.

  • Schema stability lock, SCH_S_LOCK

    This lock is acquired during compiling a query and it guarantees that the schema which is included in this query is not changed.

  • Schema modification lock, SCH_M_LOCK

    This lock is acquired during running DDL(ALTER/CREATE/DROP) and it protects that other transactions access the modified schema.

  Some DDL operation like ALTER, CREATE INDEX does not acquire SCH_M_LOCK directly. For example, CUBRID operates type checking about filtering expression when you create a filtered index; during this term, the lock which is kept to the target table is SCH-S like other type checking operations. The method has a strength to increase the concurrency by allowing other transaction's operation during DDL operation's compilation.

  However, it also has a weakness not to avoid a deadlock when DDL operations are operated at the same table at the same time. A deadlock case by SCH-S lock is as follows.

  T1	T2
  CREATE INDEX i_t_i on t( i ) WHERE i > 0	CREATE INDEX i_t_j on t(j) WHERE j > 0
  SCH-S lock during checking types of "i > 0" case.
  	SCH-S lock during checking types of "j > 0" case."j > 0"
  requesting SCH-M lock but waiting T2's SCH-S lock is released
  	requesting SCH-M lock but waiting T1's SCH-S lock is released

The following table briefly shows the lock compatibility between the locks described above. Compatibility means that the lock requester can obtain a lock while the lock holder is keeping the lock obtained for a certain object.

Lock Compatibility

(O: TRUE, X: FALSE, -: N/A)

• NULL: The status that any lock exists.

Lock Transformation Table

The below shows that INSERTed data sets X_LOCK to a row and NS_LOCK to a key when the isolation level is 4.

csql> ;au off
SET TRANSACTION ISOLATION LEVEL 4;

csql> ;au off
SET TRANSACTION ISOLATION LEVEL 4;

CREATE TABLE tbl(a INT PRIMARY KEY, b INT);
INSERT INTO tbl
  VALUES (10, 10), (30, 30), (50, 50), (70, 70);
COMMIT;

INSERT INTO tbl VALUES (20, 20);

SELECT * FROM tbl WHERE a <= 20;

SELECT * FROM tbl WHERE a > 25;

UPDATE tbl SET b=100 WHERE a > 25 AND a < 40;

COMMIT;

INSERT INTO tbl VALUES (22, 22);

The below is an example that T1 waits a lock until T2 commits updated data when isolation level is 4.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 4;

Isolation level set to:
REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 4;

Isolation level set to:
REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES.

$ cubrid lockdb demodb

*** Lock Table Dump ***

...

Object Lock Table:
      Current number of objects which are locked    = 0
      Maximum number of objects which can be locked = 10000

...

csql> SELECT nation_code, gold FROM participant WHERE nation_code='USA';

 nation_code                  gold
======================================
'USA'                          36
'USA'                          37
'USA'                          44
'USA'                          37
'USA'                          36

$ cubrid lockdb demodb

*** Lock Table Dump ***

...

Object type: Root class.
LOCK HOLDERS:
  Tran_index =   2, Granted_mode =  IS_LOCK, Count =   1, Nsubgranules =  1

Object type: Class = participant.
LOCK HOLDERS:
  Tran_index =   2, Granted_mode =  IS_LOCK, Count =   2, Nsubgranules =  0

csql> UPDATE participant SET gold = 11 WHERE nation_code = 'USA';

csql> SELECT nation_code, gold FROM participant WHERE nation_code='USA';

/* no results until transaction 2 releases a lock */

$ cubrid lockdb demodb

*** Lock Table Dump ***

...

Object type: Instance of class ( 0|   551|   7) = participant.
LOCK HOLDERS:
    Tran_index =   3, Granted_mode =   X_LOCK, Count =   2

...

Object type: Root class.
LOCK HOLDERS:
  Tran_index =   3, Granted_mode =  IX_LOCK, Count =   1, Nsubgranules =  3

NON_2PL_RELEASED:
  Tran_index =   2, Non_2_phase_lock =  IS_LOCK

...

Object type: Class = participant.
LOCK HOLDERS:
  Tran_index =   3, Granted_mode =  IX_LOCK, Count =   3, Nsubgranules =  5
  Tran_index =   2, Granted_mode =  IS_LOCK, Count =   2, Nsubgranules =  0

csql> COMMIT;
Execute OK. (0.000192 sec)

nation_code                  gold
=================================
'USA'                          11
'USA'                          11
'USA'                          11
'USA'                          11
'USA'                          11

$ cubrid lockdb demodb

...

Object type: Root class.
LOCK HOLDERS:
  Tran_index =   2, Granted_mode =  IS_LOCK, Count =   1, Nsubgranules =  1

Object type: Class = participant.
LOCK HOLDERS:
  Tran_index =   2, Granted_mode =  IS_LOCK, Count =   3, Nsubgranules =  0

...

csql> COMMIT;
Execute OK. (0.000192 sec)

$ cubrid lockdb demodb

...

Object Lock Table:
        Current number of objects which are locked    = 0
        Maximum number of objects which can be locked = 10000

Transaction Deadlock¶

A deadlock is a state in which two or more transactions wait at once for another transaction's lock to be released. CUBRID resolves the problem by rolling back one of the transactions because transactions in a deadlock state will hinder the work of another transaction. The transaction to be rolled back is usually the transaction which has made the least updates; it is usually the one that started more recently. As soon as a transaction is rolled back, the lock held by the transaction is released and other transactions in a deadlock are permitted to proceed.

It is impossible to predict such deadlocks, but it is recommended that you reduce the range to which lock is applied by setting the index, shortening the transaction, or setting the transaction isolation level as low in order to decrease such occurrences.

Note that if you configure the value of error_log_level, which indicates the severity level, to NOTIFICATION, information on lock is stored in error log file of server upon deadlock occurrences.

In the following error log file, (1) indicates a table name which causes deadlock state and (2) indicates an index name.

demodb_20111102_1811.err
    ...
    OID = -532| 520| 1
(1) Object type: Index key of class ( 0| 417| 7) = tbl.
    BTID = 0| 123| 530
(2) Index Name : i_tbl_col1
    Total mode of holders = NS_LOCK, Total mode of waiters = NULL_LOCK.
    Num holders= 1, Num blocked-holders= 0, Num waiters= 0
    LOCK HOLDERS:
    Tran_index = 2, Granted_mode = NS_LOCK, Count = 1
...

Example

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> set transaction isolation level 6;

Isolation level set to:
SERIALIZABLE

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> set transaction isolation level 6;

Isolation level set to:
SERIALIZABLE

csql> CREATE TABLE lock_tbl(host_year integer, nation_code char(3));
csql> INSERT INTO lock_tbl VALUES (2004, 'KOR');
csql> INSERT INTO lock_tbl VALUES (2004, 'USA');
csql> INSERT INTO lock_tbl VALUES (2004, 'GER');
csql> INSERT INTO lock_tbl VALUES (2008, 'GER');
csql> COMMIT;

csql> SELECT * FROM lock_tbl;

    host_year  nation_code
===================================
         2004  'KOR'
         2004  'USA'
         2004  'GER'
         2008  'GER'

csql> SELECT * FROM lock_tbl;

    host_year  nation_code
===================================
         2004  'KOR'
         2004  'USA'
         2004  'GER'
         2008  'GER'

csql> DELETE FROM lock_tbl WHERE host_year=2008;

/* no result until transaction 2 releases a lock */

$ cubrid lockdb demodb

*** Lock Table Dump ***

...


Object type: Class = lock_tbl.
LOCK HOLDERS:
    Tran_index =   2, Granted_mode =   S_LOCK, Count =   2, Nsubgranules =  0

BLOCKED LOCK HOLDERS:
    Tran_index =   1, Granted_mode =   S_LOCK, Count =   3, Nsubgranules =  0
    Blocked_mode = SIX_LOCK
    Start_waiting_at = Fri Feb 12 14:22:58 2010
    Wait_for_nsecs = -1

csql> INSERT INTO lock_tbl VALUES (2004, 'AUS');

ERROR: Your transaction (index 1, dba@ 090205|4760) has been unilaterally aborted by the system.

/* System rolled back the transaction 1 to resolve a deadlock */

$ cubrid lockdb demodb

*** Lock Table Dump ***

Object type: Class = lock_tbl.
LOCK HOLDERS:
    Tran_index =   2, Granted_mode = SIX_LOCK, Count =   3, Nsubgranules =  0

Transaction Lock Timeout¶

CUBRID provides the lock timeout feature, which sets the waiting time for the lock until the transaction lock setting is allowed.

If the lock is allowed within the lock timeout, CUBRID rolls back the transaction and outputs an error message when the timeout has passed. If a transaction deadlock occurs within the lock timeout, CUBRID rolls back the transaction whose waiting time is closest to the timeout.

Setting the Lock Timeout

The system parameter lock_timeout in the $CUBRID/conf/cubrid.conf file or the SET TRANSACTION statement sets the timeout (in seconds) during which the application will wait for the lock and rolls back the transaction and outputs an error message when the specified time has passed. The default value of the lock_timeout parameter is -1, which means the application will wait indefinitely until the transaction lock is allowed. Therefore, the user can change this value depending on the transaction pattern of the application. If the lock timeout value has been set to 0, an error message will be displayed as soon as a lock occurs.

SET TRANSACTION LOCK TIMEOUT timeout_spec [ ; ]
timeout_spec:
- INFINITE
- OFF
- unsigned_integer
- variable

• INFINITE : Wait indefinitely until the transaction lock is allowed. Has the same effect as setting the system parameter lock_timeout to -1.
• OFF : Do not wait for the lock, but roll back the transaction and display an error message. Has the same effect as setting the system parameter lock_timeout to 0.
• unsigned_integer : Set in seconds. Wait for the transaction lock for the specified time period.
• variable : A variable can be specified. Wait for the transaction lock for the value stored by the variable.

Example 1

vi $CUBRID/conf/cubrid.conf
...
lock_timeout = 10s
...

Example 2

SET TRANSACTION LOCK TIMEOUT 10;

Checking the Lock Timeout

You can check the lock timeout set for the current application by using the GET TRANSACTION statement, or store this value in a variable.

GET TRANSACTION LOCK TIMEOUT [ { INTO | TO } variable ] [ ; ]

Example

GET TRANSACTION LOCK TIMEOUT;

         Result
===============
  1.000000e+001

Checking and Handling Lock Timeout Error Message

The following message is displayed if lock timeout occurs in a transaction that has been waiting for another transaction's lock to be released.

Your transaction (index 2, user1@host1|9808) timed out waiting on IX_LOCK lock on class tbl. You are waiting for
user(s) user1@host1|csql(9807), user1@host1|csql(9805) to finish.

• Your transaction(index 2 ...): This means that the index of the transaction that was rolled back due to timeout while waiting for the lock is 2. The transaction index is a number that is sequentially assigned when the client connects to the database server. You can also check this number by executing the cubrid lockdb utility.
• (... user1@host1|9808): cub_user is the login ID of the client and the part after @ is the name of the host where the client was running. The part after| is the process ID (PID) of the client.
• IX_LOCK: This means the exclusive lock set on the object to perform data update. For details, see Lock Mode Types And Compatibility.
• user1@host1|csql(9807), user1@host1|csql(9805): Another transactions waiting for termination to lock IX_LOCK

That is, the above lock error message can be interpreted as meaning that "Because another client is holding X_LOCK on a specific row in the participant table, transaction 3 which running on the host cdbs006.cub waited for the lock and was rolled back as the timeout has passed". If you want to check the lock information of the transaction specified in the error message, you can do so by using the cubrid lockdb utility to search for the OID value (ex: 0|636|34) of a specific row where the X_LOCK is set currently to find the transaction ID currently holding the lock, the client program name and the process ID (PID). For details, see Checking Lock Status. You can also check the transaction lock information in the CUBRID Manager.

You can organize the transactions by checking uncommitted queries through the SQL log after checking the transaction lock information in the manner described above. For information on checking the SQL log, see Broker Logs.

Also, you can forcefully stop problematic transactions by using the cubrid killtran utility. For details, see Killing Transactions.

SET TRANSACTION ISOLATION LEVEL¶

You can set the level of transaction isolation by using isolation_level and the SET TRANSACTION statement in the $CUBRID/conf/cubrid.conf. The level of REPEATABLE READ CLASS and READ UNCOMMITTED INSTANCES are set by default, which indicates the level 3 through level 1 to 6. For details, see Database Concurrency.

SET TRANSACTION ISOLATION LEVEL isolation_level_spec ;

isolation_level_spec:
    SERIALIZABLE | 6
    REPEATABLE READ SCHEMA, REPETABLE READ INSTANCES | 5
    REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES | CURSOR STABILITY | 4
    REPEATABLE READ SCHEMA, READ UNCOMMITTED INSTANCES | 3
    READ COMMITTED SCHEMA, READ COMMITTED INSTANCES | 2
    READ COMMITTED SCHEMA, READ UNCOMMITTED INSTANCES | 1

Example 1

vi $CUBRID/conf/cubrid.conf
...

isolation_level = 3
...

-- or

isolation_level = "TRAN_COMMIT_CLASS_UNCOMMIT_INSTANCE"

Example 2

SET TRANSACTION ISOLATION LEVEL 3;
-- or
SET TRANSACTION ISOLATION LEVEL READ COMMITTED CLASS,READ UNCOMMITTED INSTANCES;

The following table shows the isolation levels from 1 to 6. It consists of table schema (row) and isolation level. For the unsupported isolation level, see Combination of Unsupported Isolation Level.

Levels of Isolation Supported by CUBRID

If the transaction level is changed in an application while a transaction is executed, the new level is applied to the rest of the transaction being executed. Therefore, some object locks that have already been obtained may be released during the transaction while the new isolation level is applied. For this reason, it is recommended that the transaction isolation level be modified when the transaction starts (after commit, rollback or system restart) because an isolation level which has already been set does not apply to the entire transaction, but can be changed during the transaction.

GET TRANSACTION ISOLATION LEVEL¶

You can assign the current isolation level to variable by using the GET TRANSACTION statement. The following is a statement that verifies the isolation level.

GET TRANSACTION ISOLATION LEVEL [ { INTO | TO } variable ] [ ; ]

GET TRANSACTION ISOLATION LEVEL;

       Result
=============
  READ COMMITTED SCHEMA, READ UNCOMMITTED INSTANCES

SERIALIZABLE¶

The highest isolation level (6). Problems concerning concurrency (e.g. dirty read, non-repeatable read, phantom read, etc.) do not occur.

The following are the rules of this isolation level:

• Transaction T1 cannot read or modify the record being updated by another transaction T2.
• Transaction T1 cannot read or modify the record being viewed by another transaction T2.
• Another transaction T2 cannot insert a new record into table A while transaction T1 is retrieving the records of table A.

This isolation level follows a two-phase locking protocol for shared and exclusive lock: the lock is held until the transaction ends even after the operation has been executed.

Example

The following example shows that another transaction cannot access the table or record while one transaction is reading or updating the object when the transaction level of the concurrent transactions is SERIALIZABLE.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 6;

Isolation level set to:
SERIALIZABLE

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 6;

Isolation level set to:
SERIALIZABLE

csql> CREATE TABLE isol6_tbl(host_year integer, nation_code char(3));

csql> INSERT INTO isol6_tbl VALUES (2008, 'AUS');

csql> COMMIT;

csql> SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';

    host_year  nation_code
===================================
         2008  'AUS'

csql> INSERT INTO isol6_tbl VALUES (2004, 'AUS');

/* unable to insert a row until the tran 2 committed */

csql> COMMIT;

csql> SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';

/* unable to select rows until tran 1 committed */

csql> COMMIT;

    host_year  nation_code
===================================
         2008  'AUS'
         2004  'AUS'

csql> DELETE FROM isol6_tbl
csql> WHERE nation_code = 'AUS' and
csql> host_year=2008;

/* unable to delete rows until tran 2 committed */

csql> COMMIT;

csql> SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';

/* unable to select rows until tran 1 committed */

csql> COMMIT;

    host_year  nation_code
===================================
         2004  'AUS'

csql> ALTER TABLE isol6_tbl

/* unable to alter the table schema until tran 2 committed */

/* repeatable read is ensured while tran_1 is altering table schema */

    host_year  nation_code
===================================
         2004  'AUS'

csql> COMMIT;

csql> SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';

/* unable to access the table until tran_1 committed */

csql> COMMIT;

host_year  nation_code  gold
===================================
  2004  'AUS'           NULL

REPEATABLE READ CLASS with REPEATABLE READ INSTANCES¶

A relatively high isolation level (5). A dirty or non-repeatable read does not occur, but a phantom read may.

The following are the rules of this isolation level:

• Transaction T1 cannot read or modify the record being updated by another transaction T2.
• Transaction T1 cannot read or modify the record being viewed by another transaction T2.
• Another transaction T2 can insert a new record into table A while transaction T1 is retrieving records of table A. However, transaction T1 and T2 cannot set the lock on the same record.

This isolation level follows a two-phase locking protocol.

Example

The following example shows that phantom read may occur because another transaction can add a new record while one transaction is performing the object read when the transaction level of the concurrent transactions is REPEATABLE READ CLASS with REPEATABLE READ INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 5;

Isolation level set to:
REPEATABLE READ SCHEMA, REPEATABLE READ INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 5;

Isolation level set to:
REPEATABLE READ SCHEMA, REPEATABLE READ INSTANCES.

csql> CREATE TABLE isol5_tbl(host_year integer, nation_code char(3));
csql> CREATE UNIQUE INDEX on isol5_tbl(nation_code, host_year);

csql> INSERT INTO isol5_tbl VALUES (2008, 'AUS');
csql> INSERT INTO isol5_tbl VALUES (2004, 'AUS');

csql> COMMIT;

csql> SELECT * FROM isol5_tbl WHERE nation_code='AUS';

    host_year  nation_code
===================================
         2004  'AUS'
         2008  'AUS'

csql> INSERT INTO isol5_tbl VALUES (2004, 'KOR');
csql> INSERT INTO isol5_tbl VALUES (2000, 'AUS');

/* able to insert new rows only when locks are not conflicted */

csql> SELECT * FROM isol5_tbl WHERE nation_code='AUS';

/* phantom read may occur when tran 1 committed */

csql> COMMIT;

    host_year  nation_code
===================================
         2000  'AUS'
         2004  'AUS'
         2008  'AUS'

csql> DELETE FROM isol5_tbl
csql> WHERE nation_code = 'AUS' and
csql> host_year=2008;

/* unable to delete rows until tran 2 committed */

csql> COMMIT;

csql> SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';

/* unable to select rows until tran 1 committed */

csql> COMMIT;

    host_year  nation_code
===================================
         2000  'AUS'
         2004  'AUS'

csql> ALTER TABLE isol5_tbl ADD COLUMN gold INT;

/* unable to alter the table schema until tran 2 committed */

/* repeatable read is ensured while tran_1 is altering table schema */

csql> SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';

    host_year  nation_code
===================================
         2000  'AUS'
         2004  'AUS'

csql> COMMIT;

csql> SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';

/* unable to access the table until tran_1 committed */

csql> COMMIT;

host_year  nation_code  gold
===================================
  2000  'AUS'           NULL
  2004  'AUS'           NULL

REPEATABLE READ CLASS with READ COMMITTED INSTANCES¶

A relatively low isolation level (4). A dirty read does not occur, but non-repeatable or phantom read may. That is, transaction T1 can read another value because insert or update by transaction T2 is allowed while transaction T1 is repeatedly retrieving one object.

The following are the rules of this isolation level:

• Transaction T1 cannot read the record being updated by another transaction T2.
• Transaction T1 can update/insert record to the table being viewed by another transaction T2.
• Transaction T1 cannot change the schema of the table being viewed by another transaction T2.

This isolation level follows a two-phase locking protocol for an exclusive lock. A shared lock on a row is released immediately after it is read; however, an intent lock on a table is released when a transaction terminates to ensure repeatable read on the schema.

Example

The following example shows that a phantom or non-repeatable read may occur because another transaction can add or update a record while one transaction is performing the object read but repeatable read for the table schema update is ensured when the transaction level of the concurrent transactions is REPEATABLE READ CLASS with READ COMMITTED INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 4;

Isolation level set to:
REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 4;

Isolation level set to:
REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES.

csql> CREATE TABLE isol4_tbl(host_year integer, nation_code char(3));

csql> INSERT INTO isol4_tbl VALUES (2008, 'AUS');

csql> COMMIT;

csql> SELECT * FROM isol4_tbl;

    host_year  nation_code
===================================
         2008  'AUS'

csql> INSERT INTO isol4_tbl VALUES (2004, 'AUS');
csql> INSERT INTO isol4_tbl VALUES (2000, 'NED');

/* able to insert new rows even if tran 2 uncommitted */

csql> SELECT * FROM isol4_tbl;

/* phantom read may occur when tran 1 committed */

csql> COMMIT;

    host_year  nation_code
===================================
         2008  'AUS'
         2004  'AUS'
         2000  'NED'

csql> INSERT INTO isol4_tbl VALUES (1994, 'FRA');

csql> SELECT * FROM isol4_tbl;

/* unrepeatable read may occur when tran 1 committed */

csql> DELETE FROM isol4_tbl
csql> WHERE nation_code = 'AUS' and
csql> host_year=2008;

/* able to delete rows while tran 2 is selecting rows*/

csql> COMMIT;

    host_year  nation_code
===================================
         2004  'AUS'
         2000  'NED'
         1994  'FRA'

csql> ALTER TABLE isol4_tbl ADD COLUMN gold INT;

/* unable to alter the table schema until tran 2 committed */

/* repeatable read is ensured while tran_1 is altering table schema */

csql> SELECT * FROM isol4_tbl;

    host_year  nation_code
===================================
         2004  'AUS'
         2000  'NED'
         1994  'FRA'

csql> COMMIT;

csql> SELECT * FROM isol4_tbl;

/* unable to access the table until tran_1 committed */

csql> COMMIT;

host_year  nation_code  gold
===================================
  2004  'AUS'           NULL
  2000  'NED'           NULL
  1994  'FRA'           NULL

REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES¶

The default isolation of CUBRID (3). The concurrency level is high. A dirty, non-repeatable or phantom read may occur for the rows, but repeatable read is ensured for the table. That is, transaction T2 can read an object while transaction T1 is updating one.

The following are the rules of this isolation level:

• Transaction T1 can read the record being updated by another transaction T2.
• Transaction T1 can update/insert record to the table being viewed by another transaction T2.
• Transaction T1 cannot change the schema of the table being viewed by another transaction T2.

This isolation level follows a two-phase locking protocol for an exclusive and update lock. However, the shared lock on the rows is released immediately after it is retrieved. The intent lock on the table is released when the transaction ends to ensure repeatable reads.

Example

The following example shows that another transaction can read dirty data uncommitted by one transaction but repeatable reads are ensured for table schema update when the transaction level of the concurrent transactions is REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 3;

Isolation level set to:
REPEATABLE READ SCHEMA, READ UNCOMMITTED INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 3;

Isolation level set to:
REPEATABLE READ SCHEMA, READ UNCOMMITTED INSTANCES.

csql> CREATE TABLE isol3_tbl(host_year integer, nation_code char(3));
csql> CREATE UNIQUE INDEX on isol3_tbl(nation_code, host_year);

csql> INSERT INTO isol3_tbl VALUES (2008, 'AUS');

csql> COMMIT;

csql> SELECT * FROM isol3_tbl;

    host_year  nation_code
===================================
         2008  'AUS'

csql> INSERT INTO isol3_tbl VALUES (2004, 'AUS');
csql> INSERT INTO isol3_tbl VALUES (2000, 'NED');

/* able to insert new rows even if tran 2 uncommitted */

csql> SELECT * FROM isol3_tbl;

    host_year  nation_code
===================================
         2008  'AUS'
         2004  'AUS'
         2000  'NED'

/* dirty read may occur so that tran_2 can select new rows
   uncommitted by tran_1 */

csql> ROLLBACK;

csql> SELECT * FROM isol3_tbl;

    host_year  nation_code
===================================
         2008  'AUS'

/* unrepeatable read may occur so that selected results are different */

csql> INSERT INTO isol3_tbl VALUES (1994, 'FRA');

csql> DELETE FROM isol3_tbl
csql> WHERE nation_code = 'AUS' and
csql> host_year=2008;

/* able to delete rows even if tran 2 uncommitted */

csql> SELECT * FROM isol3_tbl;

    host_year  nation_code
===================================
         1994  'FRA'

csql> ALTER TABLE isol3_tbl ADD COLUMN gold INT;

/* unable to alter the table schema until tran 2 committed */

/* repeatable read is ensured while tran_1 is altering table schema */

csql> SELECT * FROM isol3_tbl;

    host_year  nation_code
===================================
         1994  'FRA'

csql> COMMIT;

csql> SELECT * FROM isol3_tbl;

csql> COMMIT;

host_year  nation_code  gold
===================================
  1994  'FRA'           NULL

READ COMMITTED CLASS with READ COMMITTED INSTANCES¶

A relatively low isolation level (2). A dirty read does not occur, but non-repeatable or phantom read may occur. That is, this level is similar to REPEATABLE READ CLASS with READ COMMITTED INSTANCES (level 4) described above, but works differently for table schema. Non-repeatable read due to a table schema update may occur because another transaction T2 can change the schema of the table being viewed by the transaction T1.

The following are the rules of this isolation level:

• Transaction T1 cannot read the record being updated by another transaction T2.
• Transaction T1 can update/insert a record to the table being viewed by another transaction T2.
• Transaction T1 can change the schema of the table being viewed by another transaction T2.

This isolation level follows a two-phase locking protocol for an exclusive lock. However, non-repeatable read may occur because the shared lock on the rows is released immediately after it is retrieved and the intent lock on the table is released immediately as well.

Example

The following example shows that phantom or non-repeatable read for the record as well as for the table schema may occur because another transaction can add or update a new record while one transaction is performing the object read when the transaction level of the concurrent transactions is READ COMMITTED CLASS with READ COMMITTED INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 2;

Isolation level set to:
READ COMMITTED SCHEMA, READ COMMITTED INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 2;

Isolation level set to:
READ COMMITTED SCHEMA, READ COMMITTED INSTANCES.

csql> CREATE TABLE isol2_tbl(host_year integer, nation_code char(3));
csql> CREATE UNIQUE INDEX on isol2_tbl(nation_code, host_year);

csql> INSERT INTO isol2_tbl VALUES (2008, 'AUS');

csql> COMMIT;

csql> SELECT * FROM isol2_tbl;

    host_year  nation_code
===================================
         2008  'AUS'

csql> INSERT INTO isol2_tbl VALUES (2004, 'AUS');
csql> INSERT INTO isol2_tbl VALUES (2000, 'NED');

/* able to insert new rows even if tran 2 uncommitted */

csql> SELECT * FROM isol2_tbl;

/* phantom read may occur when tran 1 committed */

csql> COMMIT;

    host_year  nation_code
===================================
         2008  'AUS'
         2004  'AUS'
         2000  'NED'

csql> INSERT INTO isol2_tbl VALUES (1994, 'FRA');

csql> SELECT * FROM isol2_tbl;

/* unrepeatable read may occur when tran 1 committed */

csql> DELETE FROM isol2_tbl
csql> WHERE nation_code = 'AUS' and
csql> host_year=2008;

/* able to delete rows even if tran 2 uncommitted */

csql> COMMIT;

    host_year  nation_code
===================================
         2004  'AUS'
         2000  'NED'
         1994  'FRA'

csql> ALTER TABLE isol2_tbl ADD COLUMN gold INT;

/* able to alter the table schema even if tran 2 is uncommitted yet*/

/* unrepeatable read may occur so that result shows different schema */

csql> SELECT * FROM isol2_tbl;

csql> COMMIT;

host_year  nation_code  gold
===================================
  2004  'AUS'           NULL
  2000  'NED'           NULL
  1994  'FRA'           NULL

READ COMMITTED CLASS with READ UNCOMMITTED INSTANCES¶

The lowest isolation level (1). The concurrency level is the highest. A dirty, non-repeatable or phantom read may occur for the rows and a non-repeatable read may occur for the table as well. Similar to REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES (level 3) described above, but works differently for the table schema. That is, non-repeatable read due to table schema update may occur because another transaction T2 can change the schema of the table being viewed by the transaction T1.

The following are the rules of this isolation level:

• Transaction T1 can read the record being updated by another transaction T2.
• Transaction T1 can update/insert record to the table being viewed by another transaction T2.
• Transaction T1 can change the schema of the table being viewed by another transaction T2.

This isolation level follows a two-phase locking protocol for an exclusive and update lock. However, the shared lock on the rows is released immediately after it is retrieved. The intent lock on the table is released immediately after the retrieval as well.

Example

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 1;

Isolation level set to:
READ COMMITTED SCHEMA, READ UNCOMMITTED INSTANCES.

csql> ;autocommit off

AUTOCOMMIT IS OFF

csql> SET TRANSACTION ISOLATION LEVEL 1;

Isolation level set to:
READ COMMITTED SCHEMA, READ UNCOMMITTED INSTANCES.

csql> CREATE TABLE isol1_tbl(host_year integer, nation_code char(3));
csql> CREATE UNIQUE INDEX on isol1_tbl(nation_code, host_year);

csql> INSERT INTO isol1_tbl VALUES (2008, 'AUS');

csql> COMMIT;

csql> SELECT * FROM isol1_tbl;

    host_year  nation_code
===================================
         2008  'AUS'

csql> INSERT INTO isol1_tbl VALUES (2004, 'AUS');
csql> INSERT INTO isol1_tbl VALUES (2000, 'NED');

/* able to insert new rows even if tran 2 uncommitted */

csql> SELECT * FROM isol1_tbl;

    host_year  nation_code
===================================
         2008  'AUS'
         2004  'AUS'
         2000  'NED'

/* dirty read may occur so that tran_2 can select new rows
   uncommitted by tran_1 */

csql> ROLLBACK;

csql> SELECT * FROM isol1_tbl;

    host_year  nation_code
===================================
         2008  'AUS'

/* unrepeatable read may occur so that selected results are different */

csql> INSERT INTO isol1_tbl VALUES (1994, 'FRA');

csql> DELETE FROM isol1_tbl
csql> WHERE nation_code = 'AUS' and
csql> host_year=2008;

/* able to delete rows while tran 2 is selecting rows*/

csql> SELECT * FROM isol1_tbl;

    host_year  nation_code
===================================
         1994  'FRA'

csql> ALTER TABLE isol1_tbl ADD COLUMN gold INT;

/* able to alter the table schema even if tran 2 is uncommitted yet*/

/* unrepeatable read may occur so that result shows different schema */

csql> SELECT * FROM isol1_tbl;

csql> COMMIT;

host_year  nation_code  gold
====================================
  1994  'FRA'           NULL

UPDATE INCONSISTENCY¶

In this isolation level, uncommitted updates may be lost, which makes a transaction unrestorable (cannot be rolled back) because the data are committed before the end of the transaction. CUBRID does not support this isolation level because this can cause the updates made by the user to be lost.

The following are the rules of this isolation level:

• A transaction does not overwrite an object being modified by another transaction.

Combination of Unsupported Isolation Level¶

You can set customized isolation levels by using the SET TRANSACTION ISOLATION LEVE statement. However, combinations of isolation levels below are not supported. If they are used, a system error message is shown up and an isolation level closest to the one specified is chosen.

The following are unsupported isolation levels. If table schema is changed while data is selected, unrepeatable read occurs; therefore, the combinations below are not supported.

• READ COMMITTED CLASS with REPEATABLE READ INSTANCES
• READ UNCOMMITTED CLASS with REPEATABLE READ INSTANCES

Neither are isolation levels below supported because updating a row by a transaction is not allowed while table schema is changed by other transaction.

• READ UNCOMMITTED CLASS with READ COMMITTED INSTANCES
• READ UNCOMMITTED CLASS with READ UNCOMMITTED INSTANCES

How to Handle Dirty Record¶

CUBRID flushes dirty data (or dirty record) in the client buffers to the database (server) such as the following situations. In additions to those, there can be more situations where flushes can be performed.

• Dirty data can be flushed to server when a transaction is committed.
• Some of dirty data can be flushed to server when a lot of data is loaded into the client buffers.
• Dirty data of table A can be flushed to server when the schema of table A is updated.
• Dirty data of table A can be flushed to server when the table A is retrieved (SELECT)
• Some of dirty data can be flushed to server when a server function is called.

Restarting Database¶

CUBRID uses log volumes/files and database backups to restore committed or uncommitted transactions when system or media (disk) error occurs. Logs are also used to support the user-specified rollback. A log consists of a collection of sequential files created by CUBRID. The most recent log is called the active log, and the rest are called archive logs. A log file refers to both the active and archive logs.

All updates of the database are written to the log. Actually, two copies of the updates are logged. The first one is called a before image (UNDO log) and used to restore data during execution of the user-specified ROLLBACK WORK statement or during media or system errors. The second copy is an after image (REDO log) and used to re-apply the updates when media or system error occurs.

When the active log is full, CUBRID copies it to an archive log to store in the disk. The archive log is needed to restore the database when a system failure occurs.

Normal Termination or Error

CUBRID restores the database if it restarts due to a normal termination or a device error. The restore process re-applies the committed changes that have not been applied to the database and removes the uncommitted changes stored in the database. The general operation of the database resumes after the restore is completed. This restore process does not use any archive logs or database backup.

In a client/server environment, the database can restart by using the cubrid server utility.

Media Error

The user's intervention is somewhat needed to restart the database after media error occurs. The first step is to restore the database by installing a backup of a known good state. In CUBRID, the most recent log file (the one after the last backup) must be installed. This specific log (archive or active) is applied to a backup copy of the database. As with normal termination, the database can restart after restoration is committed.